﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
using INTEX.DataBase.Tools;
namespace WPFExcelReport
{

    public class JLTVAData : BaseFunction
    {
        
        public JLTVAData()
            : base()
        {

        }
        public JLTVAData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetVK02(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "WASTAMM";
            string STAFF = dwABPOS["STAFF"].ToString().Trim();
            string COLL = dwABPOS["COLL"].ToString().Trim();
            string ARTICLE = dwABPOS["ARTICLE"].ToString().Trim();
            string CUSTOMER = dwABPOS["CUSTOMER"].ToString().Trim();
            string DESCR = dwABPOS["DESCR"].ToString().Trim();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT * FROM (select VAAKOLL AS COLLECTION,VAAART AS ARTICLE,VAAKURZBEZ AS BEZ, "
                    + " nvl(d.yarn,VAABEZ) AS DESCR,VAABRTVON*0.01 AS VALIDWID, "
                    + " VAABRTBIS*0.01 AS WIDTH,VAAGEWVON*0.01 AS GSQM, "
                    + " TRIM(VAAMEGEW)||'/'||TRIM(VAAMENGEH) AS GS, "
                    + " VAAPRGR AS PRDGRP,(select substr(sddaten,2,20) "
                    + " from sddat where sdsa = '0196' and substr(sdkey,24,1)='V' "
                    + " and substr(sddaten,1,1)='A' and substr(sdkey,3,3) = VAAPRGR ) as PRDNAME, "
                    + " VAAARTGRP AS ARTGRP,(select substr(sddaten,2,20) "
                    + " from sddat where sdsa = '0178' and substr(sdkey,24,1)='V' "
                    + " and substr(sddaten,1,1)='A' and substr(sdkey,3,3) = VAAARTGRP ) as ARTNAME, "
                    + " VAATAX AS LABEL,(select substr(sddaten,2,20) "
                    + " from sddat where sdsa = '0568' and substr(sdkey,24,1)='V' "
                    + " and substr(sddaten,1,1)='A' and substr(sdkey,3,3) = VAATAX ) as LABELNAME, "
                    + " VAABUSIMODE AS BRAND,(select substr(sddaten,2,20) "
                    + " from sddat where sdsa = '0597' and substr(sdkey,24,1)='V' "
                    + " and substr(sddaten,1,1)='A' and substr(sdkey,3,3) = VAABUSIMODE ) as BRANDNAME, "
                    + " VAAALTERNAT AS STR,(select substr(sddaten,52,10) "
                    + " from sddat where sdsa = '0734' and substr(sddaten,1,1)='A'  "
                    + " and substr(sdkey,3,1) = VAAALTERNAT ) as STRNAME, "
                    + " to_number(DECODE(vaagesant1,'0','',vaagesant1))||trim(VAAGESBEZ1) "
                    + " ||to_number(DECODE(vaagesant2,0,'',vaagesant2))||TRIM(VAAGESBEZ2) "
                    + " ||to_number(DECODE(vaagesant3,0,'',vaagesant3))||TRIM(VAAGESBEZ3) "
                    + " ||to_number(DECODE(vaagesant4,0,'',vaagesant4))||TRIM(VAAGESBEZ4) "
                    + " ||to_number(DECODE(vaagesant5,0,'',vaagesant5))||TRIM(VAAGESBEZ5) as COMPOSITION, "
                    + " TO_NUMBER(b.vaffarbe) AS COLOR,b.vafbeschich AS FINISH,(select substr(sddaten,2,20) "
                    + " from sddat where sdsa = '0275' and substr(sddaten,1,1)='A'  "
                    + " and substr(sdkey,3,3) = SUBSTR(B.vafbeschich,1,3) ) as FINISHING, "
                    + " A.VAANEUBEN,trim(h.hrnname) as STAFF,to_number(c.vakbkundnr) AS CUSTOMER, "
                    + " (select nvl(trim(k.kdkname),trim(k.kdname1)) from kdstamm k where c.vakbmandant = k.kdmandant "
                    + " and c.vakbkundnr = k.kdkundnr) as CUNAME, "
                    + " trim(c.vakbkdartnr) AS KDART,trim(c.vakbkdartbez) AS KDARTBEZ, "
                    + " trim(c.vakbkdfarbnr) AS KDARTCOLOR "
                    + " from vaart a,vafarben b,vakdbez c,hrstamm h, "
                    + " (select TRIM(T1.TXTISIND) AS ART,TRIM(T2.TXTKTEXT) AS YARN "
                    + " from txtind T1,TXTKON T2 where T1.txtitxtart = 'VAD' "
                    + " AND T1.TXTIMANDANT = T2.TXTKMANDANT AND T1.TXTIPIND = T2.TXTKPIND) d "
                    + " where a.vaamandant = b.vafmandant and a.vaakoll = b.vafkoll "
                    + " and a.vaaart = b.vafart and b.vafmandant = c.vakbmandant(+) "
                    + " and b.vafkoll = c.vakbkoll(+) and b.vafart = c.vakbart(+) "
                    + " and b.vaffarbe = c.vakbfarbe(+) and a.vaakoll||trim(a.vaaart) = d.art(+) "
                    + " and a.vaamandant = h.hrmandant and a.vaaneuben = h.hrbenid "
                    + " and a.vaaart like '%" + ARTICLE + "%' "
                    + " AND (TRIM(A.VAANEUBEN) = '" + STAFF + "' OR '" + STAFF + "' = 'AAAAAAAA' ) "
                    + " AND (TRIM(A.VAAKOLL) = '" + COLL + "' OR '" + COLL + "' = 'AAAAA' )) "
                    + " WHERE DESCR LIKE '%" + DESCR + "%' "
                    + " AND (CUSTOMER = '" + CUSTOMER + "' OR '" + CUSTOMER + "' = '00000000' ) "                
                    + " ORDER BY ARTICLE,COLOR ";

                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["BEZ"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["BEZ"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["DESCR"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["DESCR"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["PRDNAME"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["PRDNAME"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["ARTNAME"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["ARTNAME"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["LABELNAME"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["LABELNAME"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["BRANDNAME"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["BRANDNAME"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["STRNAME"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["STRNAME"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["FINISHING"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["FINISHING"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["STAFF"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["STAFF"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["CUNAME"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["CUNAME"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["KDART"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["KDART"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["KDARTBEZ"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["KDARTBEZ"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["KDARTCOLOR"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["KDARTCOLOR"] = value;
                        
                    }
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }       
        public DataSet BuildJLTVAData(DataRow dwUI)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetVK02(dwUI);
                setReturn.Merge(setResult);                
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}